package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.db.DBConnect;
import com.model.Admin; 

public class AdminControl {

	/**
	 * 根据用户id和pwd更新密码
	 * @param admin_id
	 * @param admin_pwd
	 * @return
	 */
	public boolean updatePassword(int admin_id, String admin_pwd) {
		DBConnect dbc = new DBConnect();
		String sql = "{call updateAdminPassword("+ admin_id +", '"+ admin_pwd +"')}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbc.close();
		}
		return false;
	}
	
	/**
	 * 插入管理员信息
	 * @param admin
	 * @return
	 * @throws SQLException
	 */
	public boolean insertAdmin(Admin admin) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call insertAdmin('"+admin.getAdmin_num()+"','"+admin.getAdmin_pwd()+"'," +
				"'"+admin.getAdmin_name()+"','"+admin.getAdmin_sex()+"','"+admin.getAdmin_tel()+"'," +
						""+admin.getAdmin_building_id()+")}";
		/*
		 * String sql = "Insert into admin(admin_num, admin_pwd, admin_name, admin_sex, admin_tel, admin_building) " +
		 * "values('"+ admin.getAdmin_num() +"', '"+ admin.getAdmin_pwd() +"', '"+ admin.getAdmin_name() +"'" +
		 *			", '"+ admin.getAdmin_sex() +"', '"+ admin.getAdmin_tel() +"', '"+ admin.getAdmin_building_id() +"')";
		 */
				
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally {
			dbc.close();	
		}
	}
	
	/**
	 * 根据id删除管理员信息
	 * @param admin_id
	 * @return
	 */
	public boolean deleteAdmin(int admin_id)  {
		DBConnect dbc = new DBConnect();
		/*
		String sql1 = "Delete from post where admin_id = "+ id +"";
		String sql2 = "Delete from rate where admin_id = "+ id +"";
		String sql3 = "Delete from serve where admin_id = "+ id +"";
		String sql4 = "Delete from admin where admin_id = "+ id +" ";
		*/
		String sql = "{call deleteAdmin("+ admin_id +")}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("删除admin失败！");
			return false;
		} finally {
			dbc.close();	
		}
	}
	/**
	 * 更新管理员信息
	 * @param admin
	 * @return 
	 * @throws SQLException
	 */
	public boolean updateAdmin(Admin admin) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call updateAdmin("+admin.getAdmin_id()+",'"+admin.getAdmin_pwd()+"','"+admin.getAdmin_name()+"'," +
				"'"+admin.getAdmin_sex()+"','"+admin.getAdmin_tel()+"'," +
						""+admin.getAdmin_building_id()+")}";
		/*String sql = "update admin set admin_pwd = '"+ admin.getAdmin_pwd() +"', " +
			"admin_name = '"+ admin.getAdmin_name() +"', " +
				"admin_sex = '"+ admin.getAdmin_sex() +"', " +
				"admin_tel = '"+ admin.getAdmin_tel() +"', " +
				"admin_building = '"+ admin.getAdmin_building_id() +"' " +
				"where admin_id = '"+ admin.getAdmin_id() +"' ";*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally {
			dbc.close();	
		}
	}
	
	/**
	 * 根据building_id查找管理该寝室楼的管理员信息
	 * @param building_id
	 * @return
	 */
	public ArrayList<Admin> getAdminInformation(int building_id) {
		ArrayList<Admin> adminlist = new ArrayList<Admin>();
		DBConnect dbc = new DBConnect();
		/*
		String sql = "select * from admin,building " +
				"where admin.admin_building = building.building_id ";
		if(building_id > 0) {
			sql = sql + "and building.building_id = "+ building_id +" ";
		}
		*/
		String sql = "{call getAdminInformation("+ building_id +")}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Admin admin = new Admin();
				admin.setAdmin_id(rs.getInt("admin_id"));
				admin.setAdmin_num(rs.getString("admin_num"));
				admin.setAdmin_pwd(rs.getString("admin_pwd"));
				admin.setAdmin_name(rs.getString("admin_name"));
				admin.setAdmin_sex(rs.getString("admin_sex"));
				admin.setAdmin_tel(rs.getString("admin_tel"));
				admin.setAdmin_building_id(rs.getInt("building_id"));
				admin.setAdmin_building_name(rs.getString("building_name"));
				adminlist.add(admin);
			}
			return adminlist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}	
	}
	
	/**
	 * 根据id获取管理员基本信息
	 * @param admin_id
	 * @return
	 */
	public Admin getAdmin(int admin_id){
		DBConnect dbc=new DBConnect();
		String sql="{call getAdmin("+admin_id+")}";
		/*
		 * String sql="select * from admin,building where admin.admin_building = building.building_id and admin_id= "+admin_id+"";
		 */
		try{
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			Admin a=new Admin();
			if(rs.next()){
				a.setAdmin_id(rs.getInt("admin_id"));
				a.setAdmin_name(rs.getString("admin_name"));
				a.setAdmin_pwd(rs.getString("admin_pwd"));
				a.setAdmin_num(rs.getString("admin_num"));
				a.setAdmin_sex(rs.getString("admin_sex"));
				a.setAdmin_tel(rs.getString("admin_tel"));
				a.setAdmin_building_id(rs.getInt("admin_building"));
				a.setAdmin_building_name(rs.getString("building_name"));
			}
			return a;
		}catch(SQLException e){
			e.getStackTrace();
			return null;
		}finally{
			dbc.close();
		}
	}
}
